package com.liuwei.desktop.common;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

public class DBUtils {
	public static String sqlClass = "";
	public static String sqlUrl = "";
	
	public static ArrayList<HashMap<String,Object>> query(String sql) throws Exception{
		ArrayList<HashMap<String,Object>> result = new ArrayList<>();
		//连接SQLite的JDBC
		Class.forName(sqlClass);
		// 建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
		Connection conn = DriverManager.getConnection(sqlUrl);
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sql);// 查询数据
		
		//获取所有的列名
		ResultSetMetaData rsmd = rs.getMetaData();
		int count=rsmd.getColumnCount();
		String[] name=new String[count];
		for(int i=1;i<=count;i++){
			name[i-1]=rsmd.getColumnName(i);
			System.out.println(name[i-1]);
		}
		
		//将查询到的数据打印出来
		while (rs.next()) {
			HashMap<String,Object> row = new HashMap<>();
			for(int k=1;k<=count;k++){
				String columnName = name[k-1];
				Object value = rs.getObject(k);
				row.put(columnName, value);
			}
			result.add(row);
		}
		//结束数据库的连接
		rs.close();
		conn.close();
		return result;
	}
	
	public static boolean existTable(String tableName) throws Exception{
		boolean exist = false;
		// 连接SQLite的JDBC
		Class.forName(sqlClass);
		// 建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
		Connection conn = DriverManager.getConnection(sqlUrl);
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(" SELECT count(*) as existTable FROM sqlite_master WHERE type='table' and name ='"+tableName+"'; ");// 查询数据
		while (rs.next()) {// 将查询到的数据打印出来
			if("1".equals(rs.getString("existTable"))){
				exist = true;
			}
		}
		rs.close();
		conn.close();// 结束数据库的连接
		return exist;
	}
	
	public static void deleteFromTable(String deleteSql) throws Exception{
		//连接SQLite的JDBC
		Class.forName(sqlClass);
		//建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
		Connection conn = DriverManager.getConnection(sqlUrl);
		Statement stat = conn.createStatement();
		stat.executeUpdate(deleteSql);// 查询数据
		conn.close();// 结束数据库的连接
	}
	
	public static void insertTable(String insertSql) throws Exception{
		//连接SQLite的JDBC
		Class.forName(sqlClass);
		//建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
		Connection conn = DriverManager.getConnection(sqlUrl);
		Statement stat = conn.createStatement();
		stat.execute(insertSql);// 查询数据
		conn.close();// 结束数据库的连接
	}
	
	public static int insertReturnPkid(String insertSql) throws Exception{
		int pkid = -1;
		//连接SQLite的JDBC
		Class.forName(sqlClass);
		//建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
		Connection conn = DriverManager.getConnection(sqlUrl);
		Statement stat = conn.createStatement();
		stat.execute(insertSql);// 查询数据
		ResultSet rs = stat.executeQuery("select last_insert_rowid() as pkid");
		while (rs.next()) {// 将查询到的数据打印出来
			pkid = rs.getInt("pkid");
		}
		conn.close();// 结束数据库的连接
		return pkid;
	}
	
	public static void updateTable(String updateSql) throws Exception{
		//连接SQLite的JDBC
		Class.forName(sqlClass);
		//建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
		Connection conn = DriverManager.getConnection(sqlUrl);
		Statement stat = conn.createStatement();
		stat.executeUpdate(updateSql);// 查询数据
		conn.close();// 结束数据库的连接
	}
	
	public static void dropTable(String tableName) throws Exception{
		//连接SQLite的JDBC
		Class.forName(sqlClass);
		//建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
		Connection conn = DriverManager.getConnection(sqlUrl);
		Statement stat = conn.createStatement();
		stat.executeUpdate("drop table "+tableName+"; ");// 查询数据
		conn.close();// 结束数据库的连接
	}

	public static String getSqlClass() {
		return sqlClass;
	}

	public static void setSqlClass(String sqlClass) {
		DBUtils.sqlClass = sqlClass;
	}

	public static String getSqlUrl() {
		return sqlUrl;
	}

	public static void setSqlUrl(String sqlUrl) {
		DBUtils.sqlUrl = sqlUrl;
	}
	
	public static Connection getConnection() throws Exception{
		//连接SQLite的JDBC
		Class.forName(sqlClass);
		//建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
		Connection conn = DriverManager.getConnection(sqlUrl);
		return conn;
	}
}
